package com.peenongsaban.freelance.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Vector;

import com.peenongsaban.freelance.bean.RequestJobBean;
import com.peenongsaban.freelance.bean.StatusOfRequestBean;
import com.peenongsaban.freelance.connection.DatabaseConnectionPool;
import com.peenongsaban.freelance.dao.JobManageDAO;
import com.peenongsaban.freelance.meta.AuthorizeJobMeta;
import com.peenongsaban.freelance.meta.RequestJobMeta;
import com.peenongsaban.freelance.meta.StatusOfRequestMeta;
import com.peenongsaban.freelance.meta.ViewMeta;
import com.peenongsaban.freelance.utils.ConstantUtils;
import com.peenongsaban.freelance.utils.DateFormatUtils;
import com.peenongsaban.freelance.utils.ExceptionUtil;

public class JobManageDAOImpl implements JobManageDAO {

	@Override
	public boolean requestJobStatus(String idCard, String postId, double price) {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		try {
			conn.setAutoCommit(false);
			SimpleDateFormat ft = new SimpleDateFormat("ddMMyyyykkmmss");
			String statusOfRequestId = "SOJ" + idCard + ft.format(new Date());
			insertStatusOfJobTableOfRequestStatus(conn,price, statusOfRequestId);
			insertRequestJobTable(conn, idCard, postId,
					statusOfRequestId);
			insertAuthorizeJobTable(conn,idCard, postId, price);
			conn.commit();

		} catch (SQLException e) {
			try {
				conn.rollback();
				ExceptionUtil.messageException(new Throwable(), e);
			} catch (SQLException e1) {
				e1.printStackTrace();
			}

		} finally {
			try {
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return true;
	}

	@Override
	public boolean updateRequestJobStatus(String idCard, String postId,
			int requestTypeId, String authorizeStatus) {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		SimpleDateFormat ft = new SimpleDateFormat("ddMMyyyykkmmss");
		String statusOfRequestId = "SOJ" + idCard + ft.format(new Date());
		try {
			conn.setAutoCommit(false);
			RequestJobBean reqBean = getStatusActiveOfRequestJobTable(conn,
					idCard, postId);
			double priceOfBid = reqBean.getListStatusOfRequest().get(0)
					.getPriceOfBid();
			Date dateTime = reqBean.getListStatusOfRequest().get(0)
					.getDateTime();
			
			insertStatusOfJobTableOfOtherStatus(conn, idCard, postId, priceOfBid,
					statusOfRequestId, dateTime, requestTypeId);
			updateRequestJobTable(conn, idCard, postId);
			insertRequestJobTable(conn, idCard, postId,
					statusOfRequestId);
			updateAuthorizeJobTable(conn, idCard, postId, authorizeStatus);

			conn.commit();

		} catch (SQLException e) {
			try {
				conn.rollback();
				ExceptionUtil.messageException(new Throwable(), e);
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return true;
	}
	
	public boolean disApprovedRequestJobStatus(String idCard, String postId,
			int requestTypeId) {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		SimpleDateFormat ft = new SimpleDateFormat("ddMMyyyykkmmss");
		String statusOfRequestId = "SOJ" + idCard + ft.format(new Date());
		try {
			conn.setAutoCommit(false);
			RequestJobBean reqBean = getStatusActiveOfRequestJobTable(conn,
					idCard, postId);
			double priceOfBid = reqBean.getListStatusOfRequest().get(0)
					.getPriceOfBid();
			Date dateTime = reqBean.getListStatusOfRequest().get(0)
					.getDateTime();
			
			insertStatusOfJobTableOfOtherStatus(conn, idCard, postId, priceOfBid,
					statusOfRequestId, dateTime, requestTypeId);
			updateRequestJobTable(conn, idCard, postId);
			insertRequestJobTable(conn, idCard, postId,
					statusOfRequestId);
			deleteAuthorizeJobTable(conn, idCard, postId);
			conn.commit();

		} catch (SQLException e) {
			try {
				conn.rollback();
				ExceptionUtil.messageException(new Throwable(), e);
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return true;
	}

	@Override
	public boolean cancelRequestJobStatus(String idCard, String postId) {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		try {
			conn.setAutoCommit(false);
			Vector<String> list = listAllStatusOfRequest(conn, idCard, postId);
			for (String statusId : list) {
				deleteStatusOfRequestTable(conn,statusId);
			}
			
			deleteAuthorizeJobTable(conn, idCard, postId);
			conn.commit();

		} catch (SQLException e) {
			try {
				conn.rollback();
				ExceptionUtil.messageException(new Throwable(), e);
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			databaseConnection.destroy();
			databaseConnection = null;
			try {
				conn.close();
			} catch (SQLException e) {
				ExceptionUtil.messageException(new Throwable(), e);
			}
		}
		return true;
	}
	

	private void insertStatusOfJobTableOfOtherStatus(Connection con, String idCard,
			String postId, double price, String statusOfRequestId,
			Date dateTime, int requestTypeId) throws SQLException {

		// Insert job table
		PreparedStatement statement = null;
		String sqlInsertStatusOfJob = "INSERT INTO "
				+ StatusOfRequestMeta.TABLE + "("
				+ StatusOfRequestMeta.COL_STATUST_OF_REQUEST_ID + ", "
				+ StatusOfRequestMeta.COL_REQUEST_TYPE_ID + ", "
				+ StatusOfRequestMeta.COL_IS_OWNER + ","
				+ StatusOfRequestMeta.COL_DATE_TIME + ", "
				+ StatusOfRequestMeta.COL_RESPONSE_DATE_TIME + ","
				+ StatusOfRequestMeta.COL_PRICE_OF_BID + ") "
				+ "VALUES(?,?,?,?,SYSDATE(),?)";
		statement = con.prepareStatement(sqlInsertStatusOfJob);
		statement.setString(1, statusOfRequestId);
		statement.setInt(2, requestTypeId);
		statement.setString(3, ConstantUtils.FREELANE);
		statement.setDate(4, DateFormatUtils.dateUtilToDateSQL(dateTime));
		statement.setDouble(5, price);
		statement.execute();
		statement.close();
	}

	private void insertStatusOfJobTableOfRequestStatus(Connection con, double price,
			String statusOfRequestId) throws SQLException {
		PreparedStatement statement = null;
		// Insert job table
		String sqlInsertStatusOfJob = "INSERT INTO "
				+ StatusOfRequestMeta.TABLE + "("
				+ StatusOfRequestMeta.COL_STATUST_OF_REQUEST_ID + ", "
				+ StatusOfRequestMeta.COL_REQUEST_TYPE_ID + ", "
				+ StatusOfRequestMeta.COL_IS_OWNER + ","
				+ StatusOfRequestMeta.COL_DATE_TIME + ", "
				+ StatusOfRequestMeta.COL_PRICE_OF_BID + ") "
				+ "VALUES(?,?,?,SYSDATE(),?)";
		statement = con.prepareStatement(sqlInsertStatusOfJob);
		statement.setString(1, statusOfRequestId);
		statement.setInt(2, ConstantUtils.STATUS_REQUEST_OF_REQUEST_JOB);
		statement.setString(3, ConstantUtils.FREELANE);
		statement.setDouble(4, price);
		statement.execute();

		statement.close();
	}

	private void insertRequestJobTable(Connection con, String idCard,
			String postId, String statusOfRequestId) throws SQLException {
		PreparedStatement statement = null;
		// Insert job table
		String sqlRequestJob = "INSERT INTO " + RequestJobMeta.TABLE + "("
				+ RequestJobMeta.COL_ID_CARD + ", "
				+ RequestJobMeta.COL_POST_ID + ", "
				+ RequestJobMeta.COL_STATUS_OF_REQUEST_ID + ","
				+ RequestJobMeta.COL_STATUS_ACTIVE + ") " + "VALUES(?,?,?,?)";
		statement = con.prepareStatement(sqlRequestJob);
		statement.setString(1, idCard);
		statement.setString(2, postId);
		statement.setString(3, statusOfRequestId);
		statement.setString(4, ConstantUtils.STATUS_ACTIVE);
		statement.execute();
		statement.close();

	}

	private void insertAuthorizeJobTable(Connection con, String idCard,
			String postId, double price) throws SQLException {
		PreparedStatement statement = null;
		// Insert authorize job table
		String sqlAuthorize = "INSERT INTO " + AuthorizeJobMeta.TABLE + "("
				+ AuthorizeJobMeta.COL_ID_CARD + ", "
				+ AuthorizeJobMeta.COL_POST_ID + ", "
				+ AuthorizeJobMeta.COL_DATE_TIME + ","
				+ AuthorizeJobMeta.COL_STATUS_AUTHORIZE + ") "
				+ "VALUES(?,?,SYSDATE(),?)";
		statement = con.prepareStatement(sqlAuthorize);
		statement.setString(1, idCard);
		statement.setString(2, postId);
		statement.setString(3, ConstantUtils.STATUS_AUTHORIZE_REQUEST_JOB);
		statement.execute();
		statement.close();
	}

	private void updateAuthorizeJobTable(Connection con, String idCard,
			String postId, String authorizeStatus) throws SQLException {
		PreparedStatement statement = null;
		// Insert authorize job table
		String sqlAuthorize = "UPDATE " + AuthorizeJobMeta.TABLE + " SET "
				+ AuthorizeJobMeta.COL_STATUS_AUTHORIZE + " = ? , "
				+ AuthorizeJobMeta.COL_DATE_TIME + " = SYSDATE() WHERE "
				+ AuthorizeJobMeta.COL_ID_CARD + " = ? AND "
				+ AuthorizeJobMeta.COL_POST_ID + " = ? ";
		statement = con.prepareStatement(sqlAuthorize);
		statement.setString(1, authorizeStatus);
		statement.setString(2, idCard);
		statement.setString(3, postId);
		statement.execute();
		statement.close();
	}

	private void updateRequestJobTable(Connection con, String idCard,
			String postId) throws SQLException {
		PreparedStatement statement = null;
		String sql = "UPDATE " + RequestJobMeta.TABLE + " SET "
				+ RequestJobMeta.COL_STATUS_ACTIVE + " = ? WHERE "
				+ RequestJobMeta.COL_POST_ID + " = ? AND "
				+ RequestJobMeta.COL_ID_CARD + " = ? AND "
				+ RequestJobMeta.COL_STATUS_ACTIVE + " = ?";
		statement = con.prepareStatement(sql);
		statement.setString(1, ConstantUtils.STATUS_INACTIVE);
		statement.setString(2, postId);
		statement.setString(3, idCard);
		statement.setString(4, ConstantUtils.STATUS_ACTIVE);
		statement.execute();
		statement.close();
	}

	private RequestJobBean getStatusActiveOfRequestJobTable(Connection con,
			String idCard, String postId) throws SQLException {
		PreparedStatement statement = null;
		RequestJobBean bean = new RequestJobBean();
		String sql = "SELECT * FROM "
				+ ViewMeta.LIST_USER_REQUEST_JOB_IS_REQUEST_STATUS_V
				+ " WHERE " + RequestJobMeta.COL_POST_ID + " = ? AND "
				+ RequestJobMeta.COL_ID_CARD + " = ? ";
		statement = con.prepareStatement(sql);
		statement.setString(1, postId);
		statement.setString(2, idCard);
		ResultSet rs = statement.executeQuery();
		Vector<StatusOfRequestBean> vec = new Vector<StatusOfRequestBean>();
		while (rs.next()) {
			StatusOfRequestBean status = new StatusOfRequestBean();
			status.setDateTime(rs.getDate(StatusOfRequestMeta.COL_DATE_TIME));
			status.setPriceOfBid(rs
					.getDouble(StatusOfRequestMeta.COL_PRICE_OF_BID));
			vec.add(status);
		}
		bean.setListStatusOfRequest(vec);
		statement.close();
		return bean;
	}

	private void deleteStatusOfRequestTable(Connection con, String statusId) throws SQLException {
		// Delete request job table
		PreparedStatement statement = null;
		String sqlRequestJob = "DELETE FROM " + StatusOfRequestMeta.TABLE
				+ " WHERE " + StatusOfRequestMeta.COL_STATUST_OF_REQUEST_ID + " = ? ";
		statement = con.prepareStatement(sqlRequestJob);
		statement.setString(1, statusId);
		statement.execute();
		statement.close();
	}
	
	private Vector<String> listAllStatusOfRequest(Connection con, String idCard,
			String postId) throws SQLException {
		PreparedStatement statement = null;
		String sql = "SELECT * FROM "
				+ RequestJobMeta.TABLE
				+ " WHERE " + RequestJobMeta.COL_POST_ID + " = ? AND "
				+ RequestJobMeta.COL_ID_CARD + " = ? ";
		statement = con.prepareStatement(sql);
		statement.setString(1, postId);
		statement.setString(2, idCard);
		ResultSet rs = statement.executeQuery();
		Vector<String> vec = new Vector<String>();
		while (rs.next()) {
			vec.add(rs.getString(StatusOfRequestMeta.COL_STATUST_OF_REQUEST_ID));
		}
		statement.close();
		return vec;
	}
	

	private void deleteAuthorizeJobTable(Connection con, String idCard,
			String postId) throws SQLException {
		// Delete authorize job table
		PreparedStatement statement = null;
		String sqlAuthorize = "DELETE FROM " + AuthorizeJobMeta.TABLE
				+ " WHERE " + AuthorizeJobMeta.COL_ID_CARD + " = ? AND "
				+ AuthorizeJobMeta.COL_POST_ID + " = ? ";
		statement = con.prepareStatement(sqlAuthorize);
		statement.setString(1, idCard);
		statement.setString(2, postId);
		statement.execute();
		statement.close();
	}
}
